#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into checkin_rpt.rpt_student_checkin
select
-- 维度
tud.class_id,
tud.class_date,
substr(tud.class_date,1,4) as yearinfo,
substr(tud.class_date,6,2) as monthinfo,
substr(tud.class_date,9,2) as dayinfo,

-- 指标
-- 正常出勤
sum(morning_ontime_count) morning_ontime_count,
sum(afternoon_ontime_count) afternoon_ontime_count,
sum(evening_ontime_count) evening_ontime_count,
-- 出勤率
cast(cast(sum(morning_ontime_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2))   morning_ontime_ratio,
cast(cast(sum(afternoon_ontime_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2)) afternoon_ontime_ratio,
cast(cast(sum(evening_ontime_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2))   evening_ontime_ratio,
-- 迟到
sum(morning_late_count)   morning_late_count,
sum(afternoon_late_count) afternoon_late_count,
sum(evening_late_count)   evening_late_count,
-- 迟到率
cast(cast(sum(morning_late_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2))   morning_late_ratio,
cast(cast(sum(afternoon_late_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2)) afternoon_late_ratio,
cast(cast(sum(evening_late_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2))   evening_late_ratio,
--请假
sum(morning_leave_count)   morning_leave_count,
sum(afternoon_leave_count) afternoon_leave_count,
sum(evening_leave_count)   evening_leave_count,
-- 请假率
cast(cast(sum(morning_leave_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2))   morning_leave_ratio,
cast(cast(sum(afternoon_leave_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2)) afternoon_leave_ratio,
cast(cast(sum(evening_leave_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2))   evening_leave_ratio,
--旷课
sum(morning_absent_count)   morning_absent_count,
sum(afternoon_absent_count) afternoon_absent_count,
sum(evening_absent_count)   evening_absent_count,
-- 旷课率
cast(cast(sum(morning_absent_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2))   morning_absent_ratio,
cast(cast(sum(afternoon_absent_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2)) afternoon_absent_ratio,
cast(cast(sum(evening_absent_count) as decimal(38,2))/cast(sum(studying_student_count) as decimal(38,2))*100 as decimal(5,2))   evening_absent_ratio
from (select * from checkin_dwd.dim_dwd_course_table_upload_detail where content is not null and content != '开班典礼') tud
left join checkin_dws.dws_student_absent dsas on tud.class_id = dsas.class_id and tud.class_date = dsas.class_date
left join checkin_dws.dws_student_attend dsat on dsat.class_id = tud.class_id and dsat.class_date = tud.class_date
left join checkin_dws.dws_student_leave sdl on sdl.class_id = tud.class_id and sdl.class_date = tud.class_date
left join checkin_dwd.dim_dwd_class_studying_student_count ssc on ssc.class_id = tud.class_id and ssc.studying_date = tud.class_date
where studying_student_count is not null
group by tud.class_id, tud.class_date, substr(tud.class_date,1,4),substr(tud.class_date,6,2),substr(tud.class_date,9,2);
"